/*
 * Created on2022/6/11 15:54
 *
 * Copyright(c) YuZhou Big Data Laboratory CO.,Ltd, 2022.  All rights reserved.
 *
 *******************************************************************************
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 ******************************************************************************/

package org.yzbdl.lanius.core.plugins.metastore;

import cn.hutool.db.Entity;
import cn.hutool.db.handler.BeanHandler;
import org.apache.commons.lang.StringUtils;
import org.pentaho.di.core.plugins.PluginTypeInterface;
import org.yzbdl.lanius.core.exception.LaniusEngineDbException;
import org.yzbdl.lanius.core.metastore.HutoolDb;
import org.yzbdl.lanius.core.metastore.HutoolEntity;

import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;

/**
 * TODO
 *
 * @author hs@yzbdl.ac.cn
 * @version 1.0
 * @date 2019/6/11 15:54
 */
public class LdrPluginDao {

    private static LdrPluginDao ldrPluginDao = new LdrPluginDao();

    private LdrPluginDao() {
    }

    public static LdrPluginDao getInstance() {
        return LdrPluginDao.ldrPluginDao;
    }

    /**
     * 添加或修改
     *
     * @param obj
     * @return
     * @throws LaniusEngineDbException
     */
    public synchronized LdrPluginEntity save(LdrPluginEntity obj) throws LaniusEngineDbException {
        try {
            Entity entity = HutoolEntity.parseEntity(obj);
            Long id = HutoolDb.useDb().insertForGeneratedKey(entity);
            obj.setId(id.intValue());
            return obj;
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 添加或修改
     *
     * @param obj
     * @return
     * @throws LaniusEngineDbException
     */
    public synchronized LdrPluginEntity update(LdrPluginEntity obj) throws LaniusEngineDbException {
        try {
            HutoolDb.useDynamicUrlDb().update(HutoolEntity.parseEntity(obj, true, false),
                    HutoolEntity.create(obj).set("id", obj.getId()));
            return obj;
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 删除
     *
     * @param id
     */
    public synchronized void delete(Integer id) throws LaniusEngineDbException {
        try {
            HutoolDb.useDynamicUrlDb().del("ldr_plugin", "id", id);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 删除
     *
     * @param ids
     */
    public void deleteByListId(List<Integer> ids) throws LaniusEngineDbException {
        try {
            HutoolDb.useDynamicUrlDb().del("ldr_plugin", "id", ids);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 获取所有无效的插件
     *
     * @param
     * @return
     * @throws LaniusEngineDbException
     */
    public List<LdrPluginEntity> getAllByIsValidFalse() throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where is_valid = false";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 获取所有无效的插件
     *
     * @param
     * @return
     * @throws LaniusEngineDbException
     */
    public List<LdrPluginEntity> getAll() throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin ";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 获取所有的插件
     *
     * @param
     * @return
     * @throws LaniusEngineDbException
     */
    public int getLocalPluginSize(String pluginName, String pluginType) throws LaniusEngineDbException {
        try {
            StringBuffer where = new StringBuffer();
            if (StringUtils.isNotBlank(pluginName)) {
                where.append(" and name ILIKE '%" + pluginName + "%' ");
            }
            if (StringUtils.isNotBlank(pluginType)) {
                where.append(" and PLUGIN_ANNOTATION_TYPE_NAME like '%" + pluginType + "%' ");
            }
            String sql = "select * from ldr_plugin where is_valid = true" + where;
            HashSet set = new HashSet();
            List<LdrPluginEntity> list = HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class);
            for (LdrPluginEntity entry : list) {
                set.add(entry.getPluginId());
            }
            return set.size();
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 分页查询
     *
     * @return
     * @throws SQLException
     */
    public List<String> pageList(Integer limit, Integer offset, String pluginName, String pluginType)
            throws SQLException {
        StringBuffer where = new StringBuffer();
        if (StringUtils.isNotBlank(pluginName)) {
            where.append(" and name ilike '%" + pluginName + "%' ");
        }
        if (StringUtils.isNotBlank(pluginType)) {
            where.append(" and PLUGIN_ANNOTATION_TYPE_NAME ilike '%" + pluginType + "%' ");
        }
        StringBuffer sql = new StringBuffer("SELECT PLUGIN_ID from ldr_plugin where  IS_VALID = true " + where
                + "  GROUP BY PLUGIN_ID limit ? offset ?");
        List<String> list = HutoolDb.useDynamicUrlDb().query(sql.toString(), String.class, limit, offset);
        return list;
    }

    /**
     * 根据指定分类下面所有插件
     *
     * @param classificationId
     * @return
     * @throws LaniusEngineDbException
     */
    public List<LdrPluginEntity> getAllByClassificationId(Integer classificationId) throws LaniusEngineDbException {
        return null;
//        try {
//            String sql = "select * from ldr_plugin where id in (select plugin_id from ldr_plugin_classification where classification_id = ?) order by name asc, create_time desc";
//            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class, classificationId);
//        } catch (SQLException e) {
//            throw new LaniusEngineDbException(e);
//        }
    }

    /**
     * 根据指定分组下面所有插件
     *
     * @param categoryId
     * @return
     */
    public List<LdrPluginEntity> getAllByCategoryId(Integer categoryId) throws LaniusEngineDbException {
        try {
            String sql =
                    "select * from ldr_plugin where id in (select plugin_id from ldr_plugin_category where category_id = ?) order by name asc, create_time desc";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class, categoryId);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据插件全路径获取单个对象
     *
     * @param fullPath
     * @return
     * @throws LaniusEngineDbException
     */
    public List<LdrPluginEntity> getByFullPath(String fullPath) throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where full_path = ? order by id asc, create_time desc";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class, fullPath);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据ID获取单个对象
     *
     * @param pluginId
     * @return
     * @throws LaniusEngineDbException
     */
    public LdrPluginEntity getOne(Integer pluginId) throws LaniusEngineDbException {
        try {
            return HutoolDb.useDynamicUrlDb().query("select * from ldr_plugin where id = ?",
                    new BeanHandler<>(LdrPluginEntity.class), pluginId);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据插件meta_id获取所有的版本
     *
     * @param pluginId
     * @return
     * @throws LaniusEngineDbException
     */
    public List<LdrPluginEntity> getPluginVersions(String pluginId) throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where plugin_id = ? order by version desc";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class, pluginId);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据插件meta_id获取一条记录
     *
     * @param pluginId
     * @return
     * @throws LaniusEngineDbException
     */
    public LdrPluginEntity findByPluginId(String pluginId) throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where plugin_id = ? limit 1";
            LdrPluginEntity entity =
                    HutoolDb.useDynamicUrlDb().query(sql, new BeanHandler<>(LdrPluginEntity.class), pluginId);
            return entity;
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据插件meta_id获取一条记录
     *
     * @param pluginId
     * @return
     * @throws LaniusEngineDbException
     */
    public LdrPluginEntity findByPluginIdIgnoreCase(String pluginId) throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where lower(plugin_id) = ? limit 1";
            LdrPluginEntity entity =
                    HutoolDb.useDynamicUrlDb().query(sql, new BeanHandler<>(LdrPluginEntity.class), pluginId);
            return entity;
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据插件meta_id获取一条记录
     *
     * @param pluginId
     * @param version
     * @return
     * @throws LaniusEngineDbException
     */
    public LdrPluginEntity findByPluginIdIgnoreCaseWithVersion(String pluginId, String version) throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where lower(plugin_id) = ? and version = ? limit 1";
            LdrPluginEntity entity =
                    HutoolDb.useDynamicUrlDb().query(sql, new BeanHandler<>(LdrPluginEntity.class), pluginId.toLowerCase(), version);
            return entity;
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据分类ID和插件字符串ID查找唯一插件
     *
     * @param pluginStrId     插件字符串ID
     * @param pluginTypeClass 插件类型
     * @return
     */
    public LdrPluginEntity getLocalOne(String pluginStrId, String pluginTypeClass) throws LaniusEngineDbException {
        try {
            return HutoolDb.useDynamicUrlDb().query(
                    "select * from ldr_plugin where plugin_id = ? and plugin_type_name = ?",
                    new BeanHandler<>(LdrPluginEntity.class), pluginStrId, pluginTypeClass);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据分类ID和插件字符串ID查找唯一插件
     *
     * @param pluginStrId     插件字符串ID
     * @param pluginTypeClass 插件类型
     * @return
     */
    public LdrPluginEntity getLocalVersionOne(String pluginStrId, String pluginTypeClass, String version)
            throws LaniusEngineDbException {
        try {
            return HutoolDb.useDynamicUrlDb().query(
                    "select * from ldr_plugin where plugin_id = ? and plugin_type_name = ? and version = ?",
                    new BeanHandler<>(LdrPluginEntity.class), pluginStrId, pluginTypeClass, version);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据插件字符串ID查找最新插件 考虑到未来插件版本号数字会大于10，当前sql存在风险
     *
     * @param pluginStrId 插件字符串ID
     * @return
     */
    public LdrPluginEntity getLocalLeastOne(String pluginStrId) throws LaniusEngineDbException {
        try {
            return HutoolDb.useDynamicUrlDb().query(
                    "select * from ldr_plugin where plugin_id = ? order by version desc limit 1",
                    new BeanHandler<>(LdrPluginEntity.class), pluginStrId);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据版本和插件字符串ID查找唯一插件
     *
     * @param pluginStrId 插件字符串ID
     * @param version     版本
     * @return
     */
    public LdrPluginEntity getLocalVersionOne(String pluginStrId, String version) throws LaniusEngineDbException {
        try {
            return HutoolDb.useDynamicUrlDb().query("select * from ldr_plugin where plugin_id = ? and version = ?",
                    new BeanHandler<>(LdrPluginEntity.class), pluginStrId, version);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据分类ID和插件字符串ID查找唯一插件
     *
     * @param pluginStrId 插件字符串ID
     * @return
     */
    public LdrPluginEntity getLocalOne(String pluginStrId) throws LaniusEngineDbException {
        try {
            return HutoolDb.useDynamicUrlDb().query("select * from ldr_plugin where plugin_id = ?",
                    new BeanHandler<>(LdrPluginEntity.class), pluginStrId);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 获取指定分类下的插件，按使用次数倒序排序
     *
     * @param pluginTypeClass 插件类别类全称
     * @return
     * @throws LaniusEngineDbException
     */
    public List<LdrPluginEntity> getPluginsOrderByUseCount(String pluginTypeClass) throws LaniusEngineDbException {
        try {
            String sql =
                    "select * from ldr_plugin where plugin_type_name = ? and usage_count > 0 order by usage_count desc";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class, pluginTypeClass);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    // /**
    // * 获取指定分类下的插件，按使用次数倒序排序
    // *
    // * @return
    // */
    // public List<PluginEntity> getAllPluginsByUsageLastOpen() throws Exception {
    // try {
    // String sql = "select * from ldr_plugin where usage_this_open > 0";
    // return HutoolDb.useDynamicUrlDb().query(sql, PluginEntity.class);
    // } catch (SQLException e) {
    // throw new LaniusEngineDbException(e);
    // }
    // }
    //
    // /**
    // * 将所有插件本次打开使用次数置为0
    // * @throws LaniusEngineDbException
    // */
    // public synchronized void updateUsageThisOpen() throws Exception {
    // try {
    // String sql = "update ldr_plugin set usage_this_open = 0 where 1 = 1";
    // HutoolDb.useDynamicUrlDb().execute(sql);
    // } catch (SQLException e) {
    // throw new LaniusEngineDbException(e);
    // }
    // }

    /**
     * 获取本地插件，根据pluginTypeClass
     *
     * @param pluginTypeClass
     * @return
     * @throws LaniusEngineDbException
     */
    public List<LdrPluginEntity> getLocals(Class<? extends PluginTypeInterface> pluginTypeClass)
            throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where is_valid = true and plugin_type_name = ?";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class, pluginTypeClass.getName());
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据插件meta_id获取所有的版本
     *
     * @param pluginId
     * @return
     * @throws LaniusEngineDbException
     */
    public List<LdrPluginEntity> getPluginsWithNameOrId(String pluginId, String pluginName) throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where plugin_id ilike ? or name ilike ? order by plugin_id desc";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class, pluginId, pluginName);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

    /**
     * 根据指定分类的所有插件
     *
     * @param pluginAnnotationTypeName
     * @return
     */
    public List<LdrPluginEntity> getAllByPluginAnnotationTypeName(String pluginAnnotationTypeName) throws LaniusEngineDbException {
        try {
            String sql = "select * from ldr_plugin where plugin_annotation_type_name = ? order by name asc, create_time desc";
            return HutoolDb.useDynamicUrlDb().query(sql, LdrPluginEntity.class, pluginAnnotationTypeName);
        } catch (SQLException e) {
            throw new LaniusEngineDbException(e);
        }
    }

}